/*******************************************************************************

PooledRegions analysis_agrovet.do

2018.08.29 (DJ) Created

This do file runs agrovet regressions in two levels

(1) agrovet owner-shop level
(2) agrovet owner-shop-variety level

Question: Wholesale price and Markup are at the owner level, but the retail price is at the location level.
		  For now, I am just duplicating the wholesale price for each location of the same owner.

*******************************************************************************/


local filename1 "_weightedDIST20190124"
global indep std_google_vil_city_km
		
		
local filename1 "_DH5"
global indep DH_access_std

	// compile the dependent variable list (some of them are actually the agrovet-owner level variables, while others are at the shop level)
	#d;
	local agrovet_varlist "
	 sellfert av_num_fert_variety_17_w1 av_ALL_sqty_17_kg_w5 
	 sellseeds av_num_seed_variety_17_w1 av_seed_sqty_17_kg_w5
	 
	 ";
	#d cr

	 
		
	/***************************************
		Part 1. agrovet owner-shop level
	***************************************/
	
	// Pool Manyara and Kilimanjaro
		use "${analysis}/Manyara agrovet_shop_level.dta", clear
		rename av_village_id village_id
		merge m:1 village_id using "${analysis}/Manyara ViltoCities_dist.dta"
		keep if _merge==3
		keep survey_region `agrovet_varlist' google_vil_* av_district av_ward av_village pop_*
		tempfile manyara_avshop
		save `manyara_avshop'
		
		use "${analysis}/Kilimanjaro agrovet_shop_level.dta", clear
		gen survey_region = "KILIMANJARO"
	 
		keep survey_region `agrovet_varlist' google_vil_* av_district av_ward av_village pop_*
		append using `manyara_avshop'
	
	

	// generate a cluster identifier and indepedent variables
		egen village_cluster_id=group(av_district av_ward av_village)
		
		// distance proxy
		egen std_google_vil_city_km	=std(google_vil_city_km)

		// DH proxy
		gen DH_access = .
		foreach city in moshi arusha babati dodoma tanga{
			replace DH_access = 0 if !mi(google_vil_`city'_km)
		}
		foreach city in moshi arusha babati dodoma tanga{
			gen tau_`city' = 2.7*(0.9392 + 0.02019*google_vil_`city'_km)/25
			replace DH_access = DH_access + (1 + tau_`city')^(${elasticity})*pop_frac_`city'
		}
		egen DH_access_std = std(DH_access)
		replace DH_access_std = -DH_access_std
		
		
		bys survey_region : fsum `agrovet_varlist'
	
	
	
	
	
	// run regressions	
		#d;
		local i 0;
		foreach var in `agrovet_varlist'{;
			local ++i;
			local append_replace="append";
			if `i'==1 {; local append_replace="replace";};
			
			*A. no controls;
			qui: xi: reg `var' ${indep}, cluster(village_cluster_id);
			sum `var' if e(sample);
			local mean=r(mean);
			local sd=r(sd);
			quietly outreg2 using "${pool_results}/PooledRegions AVshoplevel`filename1'.xls", 
			nonote se symb(***,**,*) `append_replace' dec(2)
			addstat("mean of depvar", `mean', "sd of depvar", `sd') keep(${indep});
			
		};
		#d cr
		
		
	/****************************************************
		Part 2. agrovet owner-shop-variety level
	****************************************************/	

	local variety_outlist "price_17_USD_w5 wprice_ave_17_USD_w5 av_trans_cost_per50kg_USD_w5 markup_17_w5"
		 
		 
	// Pool Manyara and Kilimanjaro
	
		// MANYARA
			use "${analysis}/Manyara agrovet_shop_variety.dta", replace
			gen survey_region = "Manyara"
			foreach var in av_district av_ward av_village{
				replace `var' = regexr(`var', "_", " ")
				replace `var' = regexr(`var', "_", " ")
				replace `var' = regexr(`var', "-", " ")
				replace `var' = trim(`var')
			}	
			rename (av_district av_ward av_village) (survey_district survey_ward survey_village)
			tempfile avmanyara
			save `avmanyara'
			
			use "${analysis}/Manyara ViltoCities_dist.dta", clear
			foreach var in survey_district survey_ward survey_village{
				replace `var' = regexr(`var', "_", " ")
				replace `var' = regexr(`var', "_", " ")
				replace `var' = regexr(`var', "-", " ")
				replace `var' = trim(`var')
			}	
			tempfile googlevildist
			save `googlevildist'
			
			use `avmanyara', clear
			merge m:1 survey_district survey_ward survey_village using `googlevildist'
			keep if _merge==3
			//keep survey_region `agrovet_varlist' google_vil_* av_district av_ward av_village
			tempfile manyara_avshop
			save `manyara_avshop'
		
		
		
		
		// KILIMANJARO
			use "${analysis}/Kilimanjaro google_ViltoCities.dta", replace
			rename (district ward village_name) (av_district av_ward av_village)
			tempfile googlevildist
			save `googlevildist'
			
			use "${analysis}/Kilimanjaro agrovet_shop_variety.dta", replace
			merge m:1 av_district av_ward av_village using `googlevildist'
			drop if _merge==2
			
			rename (av_district av_ward av_village) (survey_district survey_ward survey_village)
			gen survey_region = "Kilimanjaro"
			
		
		//keep survey_region `agrovet_varlist' google_vil_* av_district av_ward av_village
		append using `manyara_avshop'
	
	
	bys survey_region : fsum `variety_outlist'
	
	// unique shop-level variable (Technically it's agrovet owner-level)
		egen shopunique = tag(av_shopid survey_region)
		replace av_trans_cost_per50kg_USD_w5 =. if shopunique==0
	
	// express markup as percentage point
		replace markup_17_w5 = markup_17_w5*100
		
	// generate a cluster identifier and a major indepedent variable
		cap drop village_cluster_id
		
		egen village_cluster_id=group(survey_district survey_ward survey_village)
		egen std_google_vil_city_km		=std(google_vil_city_km)
	
		// DH proxy
		gen DH_access = .
		foreach city in moshi arusha babati dodoma tanga{
			replace DH_access = 0 if !mi(google_vil_`city'_km)
		}
		foreach city in moshi arusha babati dodoma tanga{
			gen tau_`city' = 2.7*(0.9392 + 0.02019*google_vil_`city'_km)/25
			replace DH_access = DH_access + (1 + tau_`city')^(${elasticity})*pop_frac_`city'
		}
		egen DH_access_std = std(DH_access)
		replace DH_access_std = -DH_access_std
		
	
	
	// run regressions	
		#d;
		foreach var in `variety_outlist'{;
			*A. no controls;
			qui: xi: reg `var' ${indep} i.type i.brand, cluster(village_cluster_id);
			sum `var' if e(sample);
			local mean=r(mean);
			local sd=r(sd);
			quietly outreg2 using "${pool_results}/PooledRegions AVshoplevel`filename1'.xls", 
			nonote se symb(***,**,*) append dec(2)
			addstat("mean of depvar", `mean', "sd of depvar", `sd') keep(${indep});
	
		};
		#d cr
	

	
exit
